SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [paCalcularSaldosDeReestructura]

@IdReestructura INT

AS

DECLARE @Error INT
SET @Error = 0
DECLARE @Estado INT

BEGIN TRANSACTION

SELECT @Estado = (SELECT Estado FROM tb_Reestructuras WHERE IdReestructura = @IdReestructura)  

IF @Estado NOT IN(15, 16, 18) BEGIN

	-- ACTUALIZO LOS SALDOS DE LA REESTRUCTURA EDITADA
	UPDATE tb_Reestructuras SET
	-- Para que sea Cargo chequeo el Regimen Salarial de la Plaza que sea a 12
	CargoAbrir = ISNULL((SELECT COUNT(IdAperturaReestructura) FROM tb_AperturasDeReestructura AR WHERE Reestructura = @IdReestructura AND AR.RegimenSalarial = 12),0),
	CargoCerrar = ISNULL((SELECT COUNT(IdCierreReestructura) FROM tb_CierresDeReestructura CR INNER JOIN tb_Plazas P ON P.IdPlaza = CR.PlazaACerrar WHERE Reestructura = @IdReestructura AND P.RegimenSalarial = 12),0),
	SaldoCargos = ISNULL((SELECT COUNT(IdAperturaReestructura) FROM tb_AperturasDeReestructura AR WHERE Reestructura = @IdReestructura AND AR.RegimenSalarial = 12), 0) - ISNULL((SELECT COUNT(IdCierreReestructura) FROM tb_CierresDeReestructura CR INNER JOIN tb_Plazas P ON P.IdPlaza = CR.PlazaACerrar WHERE Reestructura = @IdReestructura AND P.RegimenSalarial = 12),0),
	-- Para que sea Horas chequeo el Regimen Salarial de la Plaza que sea a 13
	HorasAbrir = ISNULL((SELECT SUM(Horas) FROM tb_AperturasDeReestructura AR WHERE Reestructura = @IdReestructura AND AR.RegimenSalarial = 13),0),
	HorasCerrar = ISNULL((SELECT SUM(P.Horas) FROM tb_CierresDeReestructura CR INNER JOIN tb_Plazas P ON P.IdPlaza = CR.PlazaACerrar WHERE Reestructura = @IdReestructura AND P.RegimenSalarial = 13),0),
	SaldoHoras = ISNULL((SELECT SUM(Horas) FROM tb_AperturasDeReestructura AR WHERE Reestructura = @IdReestructura AND AR.RegimenSalarial = 13),0) - ISNULL((SELECT SUM(P.Horas) FROM tb_CierresDeReestructura CR INNER JOIN tb_Plazas P ON P.IdPlaza = CR.PlazaACerrar WHERE Reestructura = @IdReestructura AND P.RegimenSalarial = 13),0),
	-- Para que sea Puntos chequeo el Cargo Salarial Default de la Plaza
	PuntosAbrir = ISNULL((SELECT SUM(C.Puntos) FROM tb_AperturasDeReestructura AR INNER JOIN tb_Cargos C ON C.IdCargo = AR.CargoSalarialDefault WHERE Reestructura = @IdReestructura),0),
	PuntosCerrar = ISNULL((SELECT SUM(C.Puntos) FROM tb_CierresDeReestructura CR INNER JOIN tb_Plazas P ON P.IdPlaza = CR.PlazaACerrar INNER JOIN tb_Cargos C ON C.IdCargo = P.CargoSalarialDefault WHERE Reestructura = @IdReestructura),0),
	SaldoPuntos = ISNULL((SELECT SUM(C.Puntos) FROM tb_AperturasDeReestructura AR INNER JOIN tb_Cargos C ON C.IdCargo = AR.CargoSalarialDefault WHERE Reestructura = @IdReestructura),0) - ISNULL((SELECT SUM(C.Puntos) FROM tb_CierresDeReestructura CR INNER JOIN tb_Plazas P ON P.IdPlaza = CR.PlazaACerrar INNER JOIN tb_Cargos C ON C.IdCargo = P.CargoSalarialDefault WHERE Reestructura = @IdReestructura),0) 
	WHERE IdReestructura = @IdReestructura

	SET @Error = @@Error
	IF @Error <> 0 GOTO Salir

	-- ACTUALIZO LOS SALDOS GENERALES DE LA REESTRUCTURA

	-- Reestructura anterior aprobada
	DECLARE @IdReestructuraAnt INT
	DECLARE @AnioReestructuraAnt INT
	SELECT @IdReestructuraAnt = IdReestructura, @AnioReestructuraAnt = YEAR(FechaVigencia) FROM tb_Reestructuras WHERE FechaVigencia = (SELECT MAX(FechaVigencia) FROM tb_Reestructuras WHERE Estado IN(16, 18))

	-- Ejercicio del limite a chequear
	DECLARE @AnioReestructura INT
	SELECT @AnioReestructura = YEAR(FechaVigencia) FROM tb_Reestructuras WHERE IdReestructura = @IdReestructura

	DECLARE @Limite INT
	DECLARE @LimiteAnt INT
	DECLARE @IdSaldo INT

	-----------------------------------------------------------------
	-- Cargos PP 
	-----------------------------------------------------------------
	SELECT @Limite = (SELECT IdLimitePresupuestario FROM tb_LimitesPresupuestarios WHERE Ejercicio = @AnioReestructura AND UnidadDeMedida = 1 AND PartidaGenerica = 1)
	SELECT @LimiteAnt = (SELECT IdLimitePresupuestario FROM tb_LimitesPresupuestarios WHERE Ejercicio = @AnioReestructuraAnt AND UnidadDeMedida = 1 AND PartidaGenerica = 1)

	IF EXISTS(SELECT * FROM tb_SaldosDeReestructura WHERE Reestructura = @IdReestructura AND Limite = @Limite) BEGIN
		-- Hacer el Update del Saldo
		SELECT @IdSaldo = (SELECT IdSaldoDeReestructura FROM tb_SaldosDeReestructura WHERE Reestructura = @IdReestructura AND Limite = @Limite)

		UPDATE tb_SaldosDeReestructura SET Saldo = (ISNULL((SELECT Saldo FROM tb_SaldosDeReestructura WHERE Reestructura = @IdReestructuraAnt AND Limite = @LimiteAnt), 0)+(SELECT SaldoCargos FROM tb_Reestructuras WHERE IdReestructura = @IdReestructura)) WHERE IdSaldoDeReestructura = @IdSaldo
		SET @Error = @@Error
		IF @Error <> 0 GOTO Salir
	END
	ELSE BEGIN
		-- Hacer el Insert del Saldo
		INSERT tb_SaldosDeReestructura
		SELECT @IdReestructura, @Limite,
			(ISNULL((SELECT Saldo FROM tb_SaldosDeReestructura WHERE Reestructura = @IdReestructuraAnt AND Limite = @LimiteAnt), 0) + (SELECT SaldoCargos FROM tb_Reestructuras WHERE IdReestructura = @IdReestructura))
		SET @Error = @@Error
		IF @Error <> 0 GOTO Salir
	END

	-----------------------------------------------------------------
	-- Horas Sec PP
	-----------------------------------------------------------------
	SELECT @Limite = (SELECT IdLimitePresupuestario FROM tb_LimitesPresupuestarios WHERE Ejercicio = @AnioReestructura AND UnidadDeMedida = 2 AND PartidaGenerica = 1 AND Cargo = 3001)
	SELECT @LimiteAnt = (SELECT IdLimitePresupuestario FROM tb_LimitesPresupuestarios WHERE Ejercicio = @AnioReestructuraAnt AND UnidadDeMedida = 2 AND PartidaGenerica = 1 AND Cargo = 3001)

	IF EXISTS(SELECT * FROM tb_SaldosDeReestructura WHERE Reestructura = @IdReestructura AND Limite = @Limite) BEGIN
		-- Hacer el Update del Saldo
		SELECT @IdSaldo = (SELECT IdSaldoDeReestructura FROM tb_SaldosDeReestructura WHERE Reestructura = @IdReestructura AND Limite = @Limite)

		UPDATE tb_SaldosDeReestructura SET Saldo = (ISNULL((SELECT Saldo FROM tb_SaldosDeReestructura WHERE Reestructura = @IdReestructuraAnt AND Limite = @LimiteAnt), 0)+(ISNULL((SELECT SUM(Horas) FROM tb_AperturasDeReestructura WHERE RegimenSalarial = 13 AND CargoSalarialDefault = 3001 AND Reestructura = @IdReestructura), 0) - ISNULL((SELECT SUM(Horas) FROM tb_CierresDeReestructura CR INNER JOIN tb_Plazas P ON P.IdPlaza = CR.PlazaACerrar WHERE RegimenSalarial = 13 AND CargoSalarialDefault = 3001 AND CR.Reestructura = @IdReestructura), 0))) WHERE IdSaldoDeReestructura = @IdSaldo
		SET @Error = @@Error
		IF @Error <> 0 GOTO Salir
	END
	ELSE BEGIN
		-- Hacer el Insert del Saldo
		INSERT tb_SaldosDeReestructura
		SELECT @IdReestructura, @Limite,
			(ISNULL((SELECT Saldo FROM tb_SaldosDeReestructura WHERE Reestructura = @IdReestructuraAnt AND Limite = @LimiteAnt), 0) + (ISNULL((SELECT SUM(Horas) FROM tb_AperturasDeReestructura WHERE RegimenSalarial = 13 AND CargoSalarialDefault = 3001 AND Reestructura = @IdReestructura), 0) - ISNULL((SELECT SUM(Horas) FROM tb_CierresDeReestructura CR INNER JOIN tb_Plazas P ON P.IdPlaza = CR.PlazaACerrar WHERE RegimenSalarial = 13 AND CargoSalarialDefault = 3001 AND CR.Reestructura = @IdReestructura), 0)))
		SET @Error = @@Error
		IF @Error <> 0 GOTO Salir
	END

	-----------------------------------------------------------------
	-- Horas Ter PP
	-----------------------------------------------------------------
	SELECT @Limite = (SELECT IdLimitePresupuestario FROM tb_LimitesPresupuestarios WHERE Ejercicio = @AnioReestructura AND UnidadDeMedida = 2 AND PartidaGenerica = 1 AND Cargo = 4001)
	SELECT @LimiteAnt = (SELECT IdLimitePresupuestario FROM tb_LimitesPresupuestarios WHERE Ejercicio = @AnioReestructuraAnt AND UnidadDeMedida = 2 AND PartidaGenerica = 1 AND Cargo = 4001)
	
	IF EXISTS(SELECT * FROM tb_SaldosDeReestructura WHERE Reestructura = @IdReestructura AND Limite = @Limite) BEGIN
		-- Hacer el Update del Saldo
		SELECT @IdSaldo = (SELECT IdSaldoDeReestructura FROM tb_SaldosDeReestructura WHERE Reestructura = @IdReestructura AND Limite = @Limite)

		UPDATE tb_SaldosDeReestructura SET Saldo = (ISNULL((SELECT Saldo FROM tb_SaldosDeReestructura WHERE Reestructura = @IdReestructuraAnt AND Limite = @LimiteAnt), 0)+(ISNULL((SELECT SUM(Horas) FROM tb_AperturasDeReestructura WHERE RegimenSalarial = 13 AND CargoSalarialDefault = 3001 AND Reestructura = @IdReestructura), 0) - ISNULL((SELECT SUM(Horas) FROM tb_CierresDeReestructura CR INNER JOIN tb_Plazas P ON P.IdPlaza = CR.PlazaACerrar WHERE RegimenSalarial = 13 AND CargoSalarialDefault = 4001 AND CR.Reestructura = @IdReestructura), 0))) WHERE IdSaldoDeReestructura = @IdSaldo
		SET @Error = @@Error
		IF @Error <> 0 GOTO Salir
	END
	ELSE BEGIN
		-- Hacer el Insert del Saldo
		INSERT tb_SaldosDeReestructura
		SELECT @IdReestructura, @Limite,
			(ISNULL((SELECT Saldo FROM tb_SaldosDeReestructura WHERE Reestructura = @IdReestructuraAnt AND Limite = @LimiteAnt), 0) + (ISNULL((SELECT SUM(Horas) FROM tb_AperturasDeReestructura WHERE RegimenSalarial = 13 AND CargoSalarialDefault = 3001 AND Reestructura = @IdReestructura), 0) - ISNULL((SELECT SUM(Horas) FROM tb_CierresDeReestructura CR INNER JOIN tb_Plazas P ON P.IdPlaza = CR.PlazaACerrar WHERE RegimenSalarial = 13 AND CargoSalarialDefault = 4001 AND CR.Reestructura = @IdReestructura), 0)))
		SET @Error = @@Error
		IF @Error <> 0 GOTO Salir
	END

	-----------------------------------------------------------------
	-- Puntos PP
	-----------------------------------------------------------------
	SELECT @Limite = (SELECT IdLimitePresupuestario FROM tb_LimitesPresupuestarios WHERE Ejercicio = @AnioReestructura AND UnidadDeMedida = 3 AND PartidaGenerica = 1)
	SELECT @LimiteAnt = (SELECT IdLimitePresupuestario FROM tb_LimitesPresupuestarios WHERE Ejercicio = @AnioReestructuraAnt AND UnidadDeMedida = 3 AND PartidaGenerica = 1)

	IF EXISTS(SELECT * FROM tb_SaldosDeReestructura WHERE Reestructura = @IdReestructura AND Limite = @Limite) BEGIN
		-- Hacer el Update del Saldo
		SELECT @IdSaldo = (SELECT IdSaldoDeReestructura FROM tb_SaldosDeReestructura WHERE Reestructura = @IdReestructura AND Limite = @Limite)

		UPDATE tb_SaldosDeReestructura SET Saldo = (ISNULL((SELECT Saldo FROM tb_SaldosDeReestructura WHERE Reestructura = @IdReestructuraAnt AND Limite = @LimiteAnt), 0)+(SELECT SaldoPuntos FROM tb_Reestructuras WHERE IdReestructura = @IdReestructura)) WHERE IdSaldoDeReestructura = @IdSaldo
		SET @Error = @@Error
		IF @Error <> 0 GOTO Salir
	END
	ELSE BEGIN
		-- Hacer el Insert del Saldo
		INSERT tb_SaldosDeReestructura
		SELECT @IdReestructura, @Limite,
			(ISNULL((SELECT Saldo FROM tb_SaldosDeReestructura WHERE Reestructura = @IdReestructuraAnt AND Limite = @LimiteAnt), 0) + (SELECT SaldoPuntos FROM tb_Reestructuras WHERE IdReestructura = @IdReestructura))
		SET @Error = @@Error
		IF @Error <> 0 GOTO Salir
	END
END

Salir:
IF @Error = 0 
	COMMIT TRANSACTION
ELSE
	ROLLBACK TRANSACTION







GO
GRANT EXECUTE ON  [dbo].[paCalcularSaldosDeReestructura] TO [Reestructura]
GO
